﻿using CodeHelper.Common;
using CodeHelper.Models;
using Dapper;
using Microsoft.AspNetCore.Hosting;
using MySql.Data.MySqlClient;
using RazorEngine;
using RazorEngine.Templating; // For extension methods.
using System.Collections.Generic;
using System.Linq;

namespace CodeHelper.Manager
{
    public class ModelManager
    {
        /// <summary>
        /// 获取数据库列表
        /// </summary>
        /// <returns></returns>
        public static List<string> GetDbs()
        {
            string showdbSql = "SHOW DATABASES;";

            List<string> dbNames;

            using (var dbconn = DbHelper.GetConn())
            {
                dbNames = dbconn.Query<string>(showdbSql).ToList();
            }
            return dbNames;
        }

        /// <summary>
        /// 获取数据库下的数据表
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public static List<string> GetTables(string dbName)
        {
            List<string> tableNames;

            string sql = $@"select table_name from information_schema.tables where table_schema='{dbName}'";

            using (var dbconn = new MySqlConnection(DbHelper.ConnInUse))
            {
                tableNames = dbconn.Query<string>(sql).ToList();
            }

            return tableNames;
        }

        /// <summary>
        /// 获取表参数详情
        /// </summary>
        /// <param name="dbName"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private static TableInfo GetParameterInfos(string dbName, string tableName)
        {
            TableInfo tableInfo = new TableInfo(tableName);
            tableInfo.Parameters = new List<DbParamInfo>();
            tableInfo.Desc = GetTableDesc(dbName,tableName);

            using (var conn = DbHelper.GetConn())
            {
                string sql = $@"
                                  SELECT 
                                  `information_schema`.`COLUMNS`.`COLUMN_NAME`,
                                  `information_schema`.`COLUMNS`.`DATA_TYPE`,
                                  `information_schema`.`COLUMNS`.`COLUMN_COMMENT`
                                  FROM `information_schema`.`COLUMNS`
                                  WHERE `information_schema`.`COLUMNS`.`TABLE_SCHEMA` ='{dbName}' 
	                              and `information_schema`.`COLUMNS`.`TABLE_NAME` = '{tableName}'
                            ";
                using (var reader = conn.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        var paramName = reader["COLUMN_NAME"].ToString();
                        var paramType = reader["DATA_TYPE"].ToString();
                        var desc = reader["COLUMN_COMMENT"].ToString();

                        tableInfo.Parameters.Add(new DbParamInfo
                        {
                            ParamName = paramName,
                            ParamType = GetCLRType(paramType),
                            Desc = desc
                        });
                    }
                }

                return tableInfo;
            }
        }


        /// <summary>
        /// 获取表备注
        /// </summary>
        /// <param name="dbName"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private static string GetTableDesc(string dbName,string tableName)
        {
            string tableDesc = "";
            string sql = $@"select table_comment from information_schema.tables where table_schema=@DBNAME and table_name = @TABLENAME";

            using (var dbconn = new MySqlConnection(DbHelper.ConnInUse))
            {
                tableDesc = dbconn.QueryFirstOrDefault<string>(sql,new { DBNAME =dbName,TABLENAME = tableName});
            }

            return tableDesc;
        }

        /// <summary>
        ///  生成代码
        /// </summary>
        /// <param name="dbName"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static string GenerateModel(string dbName,string tableName)
        {
            var data = GetParameterInfos(dbName, tableName);
            var result = Engine.Razor.Run("templateKey", null, data);
            return result;
        }

        /// <summary>
        /// 根据数据库类型获取字段类型
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        private static string GetCLRType(string dbType)
        {
            switch (dbType)
            {
                case "tinyint":
                case "smallint":
                case "mediumint":
                case "int":
                case "integer":
                    return "int";
                case "bigint":
                    return "long";
                case "double":
                    return "double";
                case "float":
                    return "float";
                case "decimal":
                    return "decimal";
                case "numeric":
                case "real":
                    return "decimal";
                case "bit":
                    return "bool";
                case "date":
                case "time":
                case "year":
                case "datetime":
                case "timestamp":
                    return "DateTime";
                case "tinyblob":
                case "blob":
                case "mediumblob":
                case "longblog":
                case "binary":
                case "varbinary":
                    return "byte[]";
                case "char":
                case "varchar":
                case "tinytext":
                case "text":
                case "mediumtext":
                case "longtext":
                    return "string";
                case "point":
                case "linestring":
                case "polygon":
                case "geometry":
                case "multipoint":
                case "multilinestring":
                case "multipolygon":
                case "geometrycollection":
                case "enum":
                case "set":
                default:
                    return dbType;
            }
        }

    }
}
